package org.wangtian.youxueban.excel.util;

import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.CellFormat;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.wangtian.youxueban.excel.work.WeekDataBo;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class Excel2ExcelUtil {
    private static int maxWeek =  (6+5+4+4);
    static List<WeekDataBo> fewDataBos = new ArrayList<>();

    static {
//        fewDataBos.add(new WeekDataBo(3, 1, 0d));
//        fewDataBos.add(new WeekDataBo(5, 1, 0d));
        fewDataBos.add(new WeekDataBo(6, 19, 0d));
        fewDataBos.add(new WeekDataBo(10, 20, 0d));
        fewDataBos.add(new WeekDataBo(10, 21, 0d));
        fewDataBos.add(new WeekDataBo(10, 23, 0d));
        fewDataBos.add(new WeekDataBo(14, 20, 0d));
        fewDataBos.add(new WeekDataBo(14, 21, 0d));
        fewDataBos.add(new WeekDataBo(14, 23, 0d));
        fewDataBos.add(new WeekDataBo(18, 20, 0d));
        fewDataBos.add(new WeekDataBo(18, 21, 0d));
        fewDataBos.add(new WeekDataBo(18, 23, 0d));
//        fewDataBos.add(new WeekDataBo(18, 20, 0d));
//        fewDataBos.add(new WeekDataBo(18, 21, 0d));
//        fewDataBos.add(new WeekDataBo(18, 23, 0d));
    }

    public static void main(String[] args) {
        List<String> list = getFileList("C:\\Users\\Administrator\\Desktop\\游学班统计\\学员\\");
        Excel2ExcelUtil.read(list);
    }

    public static List<String> getFileList(String path) {
        List<String> list = new ArrayList();
        try {
            File file = new File(path);
            String[] filelist = file.list();
            for (int i = 0; i < filelist.length; i++) {
                list.add(path + "\\" + filelist[i]);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public static void read(List<String> fileFullNames) {
        int x = 0;
        for (String file : fileFullNames) {
            if (x == 1) {
//                break;
            }
            System.out.println("############begin#################");
            System.out.println(file);
            System.out.println("############begin#################");
            File f = new File(file);    //工作薄
            Workbook workbook = null;
            try {
                workbook = Workbook.getWorkbook(f);
            } catch (IOException e) {
                e.printStackTrace();
            } catch (BiffException e) {
                e.printStackTrace();
            }
            //2:获取第一个工作表sheet
            Sheet sheet = workbook.getSheet(1);
            //3:获取数据
            System.out.println("行：" + sheet.getRows());
            System.out.println("列：" + sheet.getColumns());
            List<WeekDataBo> weekDataBos = new ArrayList<>();
//            Double[][] weekDataBoArrays = new Double[12][maxWeek];
            for (int i = 3; i < 12; i++) {
                for (int j = 6; j < maxWeek; j++) {
                    Cell cell = sheet.getCell(j, i); //j第几列，i第几行
//                System.out.print(cell.getContents() + " ");
                    double value = 0;
                    if (cell.getContents() != null && !"".equals(cell.getContents().trim())) {
                        String content=cell.getContents();
                        if("休".equals(cell.getContents())){
                            content="0";
                        }
                        value = Double.parseDouble(content);
                    }
                    WeekDataBo weekDataBo = new WeekDataBo(j, i, value);
                    weekDataBos.add(weekDataBo);
                }
            }


            for (WeekDataBo weekDataBo1 : fewDataBos) {
                double value = 0;
                Cell cell = sheet.getCell(weekDataBo1.getColumn(), weekDataBo1.getRow());
                if (cell.getContents() != null && !"".equals(cell.getContents().trim())) {
                    value = Double.parseDouble(cell.getContents());
                }
                weekDataBo1.setValue(value);
                weekDataBos.add(weekDataBo1);
            }
            workbook.close();
            int index = file.lastIndexOf(File.separator);
            String fileName = file.substring(index + 1, index + 7);
            if (fileName.contains(".")) {
                fileName = fileName.split("\\.")[0];
            }
            Excel2ExcelUtil.changeValues(fileName, weekDataBos);
            x++;
        }

    }


    public static void changeValues(String sheetName, List<WeekDataBo> weekDataBos) {
        File f = new File("C:\\Users\\Administrator\\Desktop\\游学班统计\\辅训\\汇总.xls");    //工作薄
        int x = 0, y = 0;
        try {
            Workbook wb = null;        //工作薄
            WritableWorkbook wwb = null;    //可写工作薄
            Sheet s = null;                    //工作表
            WritableSheet ws = null;        //可写工作表
            //构造Workbook（工作薄）对象
            wb = Workbook.getWorkbook(f);            //
            wwb = Workbook.createWorkbook(f, wb);    //打开一个副本，并把制定数据写回到原表
            for (WeekDataBo weekDataBo : weekDataBos) {
                Double value = weekDataBo.getValue();
                int column = weekDataBo.getColumn();
                int row = weekDataBo.getRow();

                //获取一个工作表
                ws = wwb.getSheet(sheetName);
                WritableCell cell = ws.getWritableCell(column, row);//获取第一个单元格
                CellFormat cf = cell.getCellFormat();//获取第一个单元格的格式
                jxl.write.Number lbl = new jxl.write.Number(column, row,
                        value, cf);
                lbl.setCellFormat(cf);//将修改后的单元格的格式设定成跟原来一样
                ws.addCell(lbl);//将改过的单元格保存到sheet
            }

            wwb.write();
            wwb.close();
            System.out.println("##########" + sheetName + "执行成功#################");
        } catch (Exception e) {
//            System.out.println("x=" + x + ",y=" + y);
            System.out.println(sheetName);
            e.printStackTrace();
            System.exit(0);
        }
    }
}
